import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime
import openpyxl

# 财务订单数据导入到分析平台中
engine = create_engine('mysql+pymysql://jsbi:jsbi-1701@47.114.55.19:9011/biv1?charset=utf8')
con = engine.connect()

# filePath = 'D:/简尚家居/excel文件/云杉/6月(1).xlsx'
filePath = 'D:/简尚家居/钉钉下载目录/6月份自营.xlsx'
wb = openpyxl.load_workbook(filePath)
#获取workbook中所有的表格
sheets = wb.get_sheet_names()

for i in range(0, len(sheets)):
    # 0->4
    df = pd.read_excel(filePath, sheet_name=i)
    debug = ''
    df.head()
    # df.rename(columns={'订单支付金额 ': '订单支付金额'}, inplace=True)
    df['交易时间'] = '6月'
    df['插入时间'] = datetime.now()
    df['导入文件名称'] = '6月份自营.xlsx'
    # 财务_京东自营_利润表
    df.to_sql(name='财务_京东自营_利润表', con=con, if_exists='append', index=False)
    print('完成一次数据保存,页:' + str(i))
    debug = ''
